﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class ContactoClienteDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function registraContactoCliente(ByVal pContactoCliente As ContactoClienteBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO contactcli(IdCliente, Rfc, Telefono, Contacto, Direccion, Status) " & _
                                            "VALUES (@IdCliente,@Rfc, @Telefono, @Contacto, @Direccion, @Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdCliente", pContactoCliente.IdCliente)
                    myComm.Parameters.AddWithValue("@Rfc", pContactoCliente.Rfc)
                    myComm.Parameters.AddWithValue("@Telefono", pContactoCliente.Telefono)
                    myComm.Parameters.AddWithValue("@Contacto", pContactoCliente.Contacto)
                    myComm.Parameters.AddWithValue("@Direccion", pContactoCliente.Direccion)
                    Select Case pContactoCliente.Status
                        Case ContactoClienteBE.StatusContactoCliente.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case ContactoClienteBE.StatusContactoCliente.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaContactoCliente(ByVal pContactoCliente As ContactoClienteBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE contactcli " & _
                                            "SET Rfc = @Rfc, Telefono = @Telefono, " & _
                                            "Contacto = @Contacto, Direccion = @Direccion, Status = @Status " & _
                                            "WHERE IdContactoCliente = @IdContactoCliente and IdCliente = @IdCliente; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Rfc", pContactoCliente.Rfc)
                    myComm.Parameters.AddWithValue("@Telefono", pContactoCliente.Telefono)
                    myComm.Parameters.AddWithValue("@Contacto", pContactoCliente.Contacto)
                    myComm.Parameters.AddWithValue("@Direccion", pContactoCliente.Direccion)
                    Select Case pContactoCliente.Status
                        Case ContactoClienteBE.StatusContactoCliente.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case ContactoClienteBE.StatusContactoCliente.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@IdContactoCliente", pContactoCliente.IdContactoCliente)
                    myComm.Parameters.AddWithValue("@IdCliente", pContactoCliente.IdCliente)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getContactoCliente(ByVal IdContactoCliente As Integer, ByVal idCliente As Integer) As ContactoClienteBE
        Dim tmpContactoCliente As ContactoClienteBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdContactoCliente,  IdCliente, Rfc, Telefono, Contacto, Direccion, Status " & _
                                            "FROM contactcli " & _
                                            "WHERE IdContactoCliente = @IdContactoCliente AND IdCliente = @IdCliente;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdContactoCliente", IdContactoCliente)
                    myComm.Parameters.AddWithValue("@IdCliente", idCliente)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpContactoCliente = ContactoClienteFact.Build(myDR)
                        Else
                            tmpContactoCliente = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpContactoCliente

        Catch ex As Exception
            Throw
        Finally
            tmpContactoCliente = Nothing
        End Try
    End Function

    Public Function getListadoContactoCliente(ByVal idCliente As Integer,
                                              ByVal pRegistroInicial As Integer, ByVal pPageSize As Integer,
                                              ByVal pSortColumn As Enumeraciones.campoOrdenamiento, ByVal pSortDirection As Enumeraciones.tipoOrdenamiento,
                                              ByRef pTotalRegistros As Integer) As List(Of ContactoClienteBE)
        Dim ContactoClienteLs As List(Of ContactoClienteBE) = New List(Of ContactoClienteBE)
        pTotalRegistros = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT SQL_CALC_FOUND_ROWS IdContactoCliente, IdCliente, Rfc, Telefono, Contacto, Direccion, Status  " & _
                                           "FROM contactcli " &
                                           "WHERE IdCliente = @idCliente " &
                                           Enumeraciones.getOrderByStatement(Enumeraciones.catalogo.ContactosClientes, pSortColumn, pSortDirection) &
                                           " LIMIT @regInic, @pageSize;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@idCliente", idCliente)
                    myComm.Parameters.AddWithValue("@regInic", pRegistroInicial)
                    myComm.Parameters.AddWithValue("@pageSize", pPageSize)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim ContactoCliente As ContactoClienteBE = ContactoClienteFact.Build(myDR)
                            If (Not ContactoCliente Is Nothing) Then
                                ContactoClienteLs.Add(ContactoCliente)
                            End If
                            ContactoCliente = Nothing
                        End While
                    End Using
                    Using myComm2 As MySqlCommand = myConn.CreateCommand()
                        myComm2.CommandType = CommandType.Text
                        myComm2.CommandText = "SELECT FOUND_ROWS();"
                        pTotalRegistros = Convert.ToInt32(myComm2.ExecuteScalar())
                    End Using
                End Using
            End Using
            Return ContactoClienteLs
        Catch ex As Exception
            Throw
        Finally
            ContactoClienteLs = Nothing
        End Try
    End Function

#End Region

End Class
